﻿using RLRT.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

namespace RLRT.Models
{
    public class RTProvider : LocalDbProvider
    {
        #region 收货

        /// <summary>
        /// 获取工令号下拉数据
        /// </summary>
        /// <param name="type">1一般 2外包</param>
        /// <returns></returns>
        public DataTable GetOrNoCboList(int type)
        {
            string sql = " select distinct prlsord from purprl where prlqty>prlqcqty+prlrtq and prlpmt='Y' ";
            if (type == 1)
            {
                sql += " and prlno in(select prhno from purprh where prhtyp in('N','S')) ";
            }
            if (type == 2)
            {
                sql += " and prlno in(select prhno from purprh where prhtyp='F') ";
            }
            sql += " order by 1 ";
            return this.Query(sql);
        }

        /// <summary>
        /// 获取可取消收货单号下拉数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetCancelRTNoList()
        {
            string sql = "select distinct rtrno from trtrtm where rtid='RN' and rtsts='50' and rtqact=0 order by rtrno ";

            return this.Query(sql);
        }

        /// <summary>
        /// 获取可报废收货单号下拉数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetReturnRTNoList()
        {
            string sql = "select distinct rtrno from trtrtm where rtid='RN' and rtsts='50' order by rtrno ";

            return this.Query(sql);
        }

        /// <summary>
        /// 获取收货单号下拉数据
        /// </summary>
        /// <param name="type">1一般 2外包</param>
        /// <returns></returns>
        public DataTable GetRTNoCboList(int type)
        {
            string sql = " select distinct rtrno from trtrtm,purprl where rtid='RN' and prlid='RL' and rtprno=prlno ";
            if (type == 1)
            {
                sql += " and rtprseq=prlseq and prlpmt='Y' and rtsts in('20','45')  and rttyp='00' ";
            }
            if (type == 2)
            {
                sql += " and rtprseq=prlseq and prlpmt='Y' and rtsts in('20','45')  and rttyp='01' ";
            }
            sql += " order by rtrno ";
            return this.Query(sql);
        }

        /// <summary>
        /// 查询工令请购清单（待收货）
        /// <param name="type">1一般 2外包</param>
        /// </summary>
        public DataTable GetPRList(int type, string orNo, string venNo)
        {
            string sql = string.Empty;
            if (type == 1)
            {
                sql = @"SELECT * FROM(
                    select prlqty-prlrtq-prlqcqty openqty,prlno,prlseq,prlsord,prltno,bomnam,bommat,prlstation,prlum,prlqty,prlrdte,prlpdte,prlvnd
                    from purprl,purprh,tsfcbom where prlpmt='Y' and prlsord=bomwno and prlsoseq=bomseq
                    and prlqty>prlqcqty+prlrtq and prlno=prhno and prhtyp='N'
                    UNION ALL
                    select prlqty-prlrtq-prlqcqty openqty,prlno,prlseq,prlsord,prltno,prloutno,prlpicno,prlstation,prlum,prlqty,prlrdte,prlpdte,prlvnd
                    from purprl,purprh where prlpmt='Y' 
                    and prlqty>prlqcqty+prlrtq and prlno=prhno and prhtyp='S' 
                    ) t where 1=1 ";
            }
            if (type == 2)
            {
                sql = @"select prlqty-prlrtq-prlqcqty openqty,prlno,prlseq,prlsord,prltno,bomnam,bommat,prlstation,prlum,prlqty,prlrdte,prlpdte from purprl,purprh,tsfcbom where prlpmt='Y' and prlsord=bomwno and prlsoseq=bomseq
                        and prlqty>prlqcqty+prlrtq and prlno=prhno and prhtyp='F' ";
            }

            sql += string.Format(" and prlsord=N'{0}' ", orNo);
            if (!string.IsNullOrWhiteSpace(venNo))
            {
                sql += string.Format(" AND prlvnd=N'{0}' ", venNo);
            }
            sql += " order by prlno,prlseq ";

            return this.Query(sql);
        }

        /// <summary>
        /// 查询收货记录详情
        /// </summary>
        public DataRow GetRTRecord(string rtNo)
        {
            string sql = string.Format(" select * from trtrtm where rtrno=N'{0}' ", rtNo);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询收货取消列表
        /// </summary>
        public DataTable GetRTCancelList(string rtNo)
        {
            var rtRocord = GetRTRecord(rtNo);
            if (rtRocord == null)
            {
                return null;
            }

            string rtType = rtRocord["rttyp"].ToString().Trim();
            string sql = string.Empty;
            if (rtType == "00" || rtType == "01") //一般/委外
            {
                sql = @"select trtrtm.*,prlvnd,prlvndm,bomnam,bommat,prlstation from trtrtm,purprl,tsfcbom where rtid='RN' and prlid='RL' and rtprno=prlno and rtprseq=prlseq and prlsoseq=bomseq and prlsord=bomwno and rtrno=N'{0}' ";
            }
            if (rtType == "02") //材料
            {
                sql = @"select trtrtm.*,prlvnd,prlvndm,prloutno bomnam,prlpicno bommat,prlstation from trtrtm,purprl where rtid='RN' and prlid='RL' and rtprno=prlno and rtprseq=prlseq and rtrno=N'{0}' ";
            }

            sql = string.Format(sql, rtNo);
            return this.Query(sql);
        }

        /// <summary>
        /// 查询退货报废列表
        /// </summary>
        public DataTable GetRTReturnList(string rtNo)
        {
            var rtRocord = GetRTRecord(rtNo);
            if (rtRocord == null)
            {
                return null;
            }

            string rtType = rtRocord["rttyp"].ToString().Trim();
            string sql = string.Empty;
            if (rtType == "00" || rtType == "01") //一般/委外
            {
                sql = @"select trtrtm.*,rtqinv-rtqact-rtqpts-rtqscrp-rtqqty openqty,prlvnd,prlvndm,prlstation,bomnam,bommat from trtrtm,purprl,tsfcbom where rtid='RN' and prlid='RL' and rtprno=prlno and rtprseq=prlseq and prlsoseq=bomseq and prlsord=bomwno and rtrno=N'{0}' ";
            }
            if (rtType == "02") //材料
            {
                sql = @"select trtrtm.*,rtqinv-rtqact-rtqpts-rtqscrp-rtqqty openqty,prlvnd,prlvndm,prlstation,prloutno bomnam,prlpicno bommat from trtrtm,purprl  where rtid='RN' and prlid='RL' and rtprno=prlno and rtprseq=prlseq and rtrno=N'{0}' ";
            }

            sql = string.Format(sql, rtNo);
            return this.Query(sql);
        }


        /// <summary>
        /// 新增收货记录sql
        /// </summary>
        public string InsertRTRecordSql(string rtNo, string rtSeq, string rtType, string prltno, string fac, string whCode, string perWeight, string shipNo, string OdNo, string ordNo, string prlstation, string venNo, string rtqty, string curr, string tax, string unit, string remark, string sysUsername, string strDate, string strTime, string rtTypeDesc, string prlseq, string sumWeight, string storageQty)
        {
            string sql = string.Format(" insert into trtrtm values ('RN',N'{0}',N'{1}','50',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}',N'{7}',N'{8}',N'{9}',N'{10}',N'{11}',' ','B01','B01',N'{12}',N'{13}',N'{25}',0,0,0,N'{14}',N'{15}',0,N'{16}',N'{17}',N'{18}',N'{19}',N'{20}',N'{21}',N'{22}',' ',N'{23}',N'{24}' )", rtNo, rtSeq, rtType, prltno, fac, whCode, perWeight, shipNo, OdNo, ordNo, prltno, prlstation, venNo, rtqty, curr, tax, unit, remark, sysUsername, strDate, strTime, rtTypeDesc, prlseq, perWeight, sumWeight, storageQty);

            return sql;
        }

        /// <summary>
        /// 更新请购收货数量sql
        /// </summary>
        public string UpdatePRQtySql(string OdNo, string prlseq, string rtqty)
        {
            string sql = string.Format(" update purprl set prlrtq=prlrtq+{2} where prlno=N'{0}' and prlseq=N'{1}' ", OdNo, prlseq, rtqty);

            return sql;
        }

        /// <summary>
        /// 减少请购收货数量sql
        /// </summary>
        public string DecreasePRQtySql(string OdNo, string prlseq, string rtqty)
        {
            string sql = string.Format(" update purprl set prlrtq=prlrtq-{2} where prlno=N'{0}' and prlseq=N'{1}' ", OdNo, prlseq, rtqty);

            return sql;
        }

        /// <summary>
        /// 取消收货记录状态sql
        /// </summary>
        public string CancelRTRecordSql(string rtNo, string seq)
        {
            string sql = string.Format(" update trtrtm set rtsts='99',rtqinv = 0 where rtrno=N'{0}' and rtseq=N'{1}' ", rtNo, seq);

            return sql;
        }

        /// <summary>
        /// 获取工件库存
        /// </summary>
        public DataTable GetInventoryByItem(string whCode, string drawNo)
        {
            string sql = string.Format("select * from inventory WITH(NOLOCK) where whcode=N'{0}' and pono=N'{1}' ", whCode, drawNo);

            return this.Query(sql);
        }

        /// <summary>
        /// 获取工件库存Sql
        /// </summary>
        public string GetInventoryByItemSql(string whCode, string drawNo)
        {
            string sql = string.Format("select * from inventory WITH(NOLOCK) where whcode=N'{0}' and pono=N'{1}' ", whCode, drawNo);

            return sql;
        }

        /// <summary>
        /// 新增工件库存Sql
        /// </summary>
        public string InsertInventorySql(string whCode, string drawNo, string orNO, string procCode, string itemNo, string qty, string um)
        {
            string sql = string.Format("insert into inventory values(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}') ", whCode, drawNo, orNO, procCode, itemNo, Util.ToDecimal(qty), um);
            return sql;
        }

        /// <summary>
        /// 更新工件库存Sql
        /// </summary>
        public string UpdateInventorySql(string whCode, string drawNo, string qty)
        {
            string sql = string.Format(" update inventory set qty=qty+{2} where whcode=N'{0}' and pono=N'{1}' ", whCode, drawNo, Util.ToDecimal(qty));

            return sql;
        }

        /// <summary>
        /// 减少工件库存Sql
        /// </summary>
        public string DecreaseInventorySql(string whCode, string drawNo, string qty)
        {
            string sql = string.Format(" update inventory set qty=qty-{2} where whcode=N'{0}' and pono=N'{1}' ", whCode, drawNo, Util.ToDecimal(qty));

            return sql;
        }

        /// <summary>
        /// 查询工件名称
        /// </summary>
        public string GetBomName(string orNo, string odNo, string prlseq)
        {
            string sql = string.Format(" select bomnam from tsfcbom,purprl where bomwno=N'{0}' and bomseq=prlsoseq and prlno=N'{1}' and prlseq=N'{2}' ", orNo, odNo, prlseq);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0]["bomnam"].ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 查询最新收货单号
        /// </summary>
        public DataTable GetNewRTNo(string fac, string whCode)
        {
            string sql = string.Format(" select * from purrnm where rtfac=N'{0}' and rtwhs=N'{1}' ", fac, whCode);

            return this.Query(sql);
        }

        /// <summary>
        /// 新增最新收货单号
        /// </summary>
        public int InsertNewRTNo(string fac, string whCode, string newRTNo)
        {
            string sql = string.Format(" insert into purrnm values(N'{0}',N'{1}',N'{2}' ", fac, whCode, newRTNo);

            return this.Execute(sql);
        }

        /// <summary>
        /// 更新最新收货单号
        /// </summary>
        public int UpdateNewRTNo(string fac, string whCode, string newRTNo)
        {
            string sql = string.Format("update purrnm set rtirno=N'{2}' where rtfac=N'{0}' and rtwhs=N'{1}' ", fac, whCode, newRTNo);

            return this.Execute(sql);
        }

        /// <summary>
        /// 更新最新收货单号sql
        /// </summary>
        public string UpdateNewRTNoSql(string fac, string whCode, string newRTNo)
        {
            string sql = string.Format("update purrnm set rtirno=N'{2}' where rtfac=N'{0}' and rtwhs=N'{1}' ", fac, whCode, newRTNo);

            return sql;
        }

        /// <summary>
        /// 查询材料请购头表信息
        /// </summary>
        public DataRow GetMaterialPRInfo(string prNo)
        {
            string sql = string.Format(" select * from purprh where prhtyp in('N','S') and prhno=N'{0}' ", prNo);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询请购单供应商信息
        /// </summary>
        public DataRow GetPRVendorInfo(string prNo)
        {
            string sql = string.Format("select distinct prlvnd,prlvndm,prlcur from purprl where prlpmt='Y' and prlno=N'{0}' ", prNo);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询请购收货清单
        /// <param name="prType"></param>
        /// </summary>
        public DataTable GetMaterialPRList(string prType, string prNo)
        {
            string sql = string.Empty;
            if (prType == "N") //一般请购收货
            {
                sql = @"select prlqty-prlrtq-prlqcqty openqty,* from purprl,tsfcbom where prlsord=bomwno and prlsoseq=bomseq and prlno=N'{0}' ";
            }
            else  //材料请购收货
            {
                sql = @"select prlqty-prlrtq-prlqcqty openqty,*,prloutno bomnam,prlpicno bommat from purprl where prlno=N'{0}' ";
            }

            sql = string.Format(sql, prNo);
            return this.Query(sql);
        }

        #endregion

        #region 入库

        /// <summary>
        /// 查询入库数据列表
        /// <param name="type">1一般 2外包</param>
        /// </summary>
        public DataTable GetStorageList(int type, string rtNo)
        {
            string sql = string.Empty;
            if (type == 2) //外包入库
            {
                sql = @"select trtrtm.*,prlvnd,prlvndm,prlstation,bomnam,bommat from trtrtm,purprl,tsfcbom where rtid='RN' and prlid='RL' and prlsord=bomwno and prlsoseq=bomseq and rtprno=prlno and rtprseq=prlseq and rtsts in('20','45') and rttyp='01' and rtrno=N'{0}' ";
            }
            else if (type == 1)
            {
                var rtRocord = GetRTRecord(rtNo);
                if (rtRocord == null)
                {
                    return null;
                }

                string rtType = rtRocord["rttyp"].ToString().Trim();

                if (rtType == "00") //一般
                {
                    sql = @"select trtrtm.*,prlvnd,prlvndm,bomnam,bommat,prlstation from trtrtm,purprl,tsfcbom where rtid='RN' and prlid='RL' and rtprno=prlno and rtprseq=prlseq and prlsoseq=bomseq and prlsord=bomwno and rtsts in('20','45') and rtrno=N'{0}' ";
                }
                if (rtType == "02") //材料
                {
                    sql = @"select trtrtm.*,prlvnd,prlvndm,prloutno bomnam,prlpicno bommat,prlstation from trtrtm,purprl where rtid='RN' and prlid='RL' and rtprno=prlno and rtprseq=prlseq and rtsts in('20','45') and rtrno=N'{0}' ";
                }
            }

            sql = string.Format(sql, rtNo);
            return this.Query(sql);
        }

        /// <summary>
        /// 更新收货入库数量sql
        /// </summary>
        public string UpdateStorageQtySql(string rtNo, string seq, string qty)
        {
            string sql = string.Format(" update trtrtm set rtsts='45',rtqact = rtqact +{2} where rtid='RN' and rtrno=N'{0}' and rtseq=N'{1}' ", rtNo, seq, qty);

            return sql;
        }

        /// <summary>
        /// 更新退货报废数量sql
        /// </summary>
        public string UpdateReturnQtySql(int type, string rtNo, string seq, string qty)
        {
            string sql = "";
            if (type == 0) //退货
            {
                sql = string.Format("update trtrtm set rtqpts = rtqpts +{0} ", qty);
            }
            else if (type == 1) //报废
            {
                sql = string.Format("update trtrtm set rtqscrp = rtqscrp +{0} ", qty);
            }
            else  //短张
            {
                sql = string.Format("update trtrtm set rtqqty = rtqqty +{0} ", qty);
            }

            sql += string.Format(" ,rtqact = rtqact +{2} where rtrno=N'{0}' and rtseq=N'{1}' ", rtNo, seq, -1 * Util.ToDecimal(qty));

            return sql;
        }

        /// <summary>
        /// 更新收货入库状态sql
        /// </summary>
        public string UpdateStorageStatusSql(string rtNo, string seq)
        {
            string sql = string.Format(" update trtrtm set rtsts='50' where rtid='RN' and rtqact+rtqpts+rtqqty=rtqinv and rtrno=N'{0}' and rtseq=N'{1}' ", rtNo, seq);

            return sql;
        }

        /// <summary>
        /// 更新请购入库数量sql
        /// </summary>
        public string UpdatePRStorageQtySql(string OdNo, string prlseq, string qty)
        {
            string sql = string.Format(" update purprl set prlqcqty = prlqcqty-{2},prlrtq = prlrtq+{2} where prlno=N'{0}' and prlseq=N'{1}' ", OdNo, prlseq, qty);

            return sql;
        }

        #endregion

        #region 领用

        /// <summary>
        /// 查询工件信息
        /// </summary>
        public DataRow GetItemInfo(string bomNo)
        {
            string sql = string.Format("select top 1 * from purprl where prltno=N'{0}' ", bomNo);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取领用工令下拉数据
        /// </summary>
        public DataTable GetPickOrNoList()
        {
            string sql = "select bomhwno from tsfcbomh where bomhqre-bomhqfi>0 order by 1 ";

            return this.Query(sql);
        }

        /// <summary>
        /// 查询BOM头表
        /// </summary>
        public DataRow GetBOMHeader(string orNo)
        {
            string sql = string.Format("select * from tsfcbomh where bomhwno=N'{0}' ", orNo);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取bom详情列表
        /// </summary>
        public DataTable GetBOMDetailList(string orNo)
        {
            string sql = string.Format("select * from tsfcbom where bomwno=N'{0}' order by bomseq", orNo);

            return this.Query(sql);
        }

        /// <summary>
        /// 查询BOM详情
        /// </summary>
        public DataRow GetBOMDetail(string orNo, string bomNo, string bomSeq)
        {
            string sql = string.Format("select * from tsfcbom where bomwno=N'{0}' and bompno=N'{1}' and bomseq=N'{2}'  ", orNo, bomNo, bomSeq);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 新增领用记录
        /// </summary>
        public string InsertPickSql(string pickNo, string seq, string whCode, string orNo, string bomSeq, string bompno, string bomnam, string bommat, string bomspc, string pickqty, string um, string pickDate, string remark)
        {
            string sql = string.Format("insert into invmi VALUES(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}',N'{7}',N'{8}',N'{9}',N'{10}',N'{11}',N'{12}') ", pickNo, seq, whCode, orNo, bomSeq, bompno, bomnam, bommat, bomspc, Util.ToInt(pickqty), um, pickDate, remark);
            return sql;
        }


        #endregion

        #region 仓库、料号

        /// <summary>
        /// 查询仓库主档
        /// </summary>
        public DataTable GetWarehouseList()
        {
            string sql = " select * from inviwm where wmid='A' order by 1 ";

            return this.Query(sql);
        }

        /// <summary>
        /// 查询仓库详情
        /// </summary>
        public DataRow GetWarehouse(string no)
        {
            string sql = string.Format(" SELECT * FROM inviwm where wmid='A' and wmwhs=N'{0}' ", no);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询料号主档
        /// </summary>
        public DataTable GetItemList()
        {
            string sql = " select * from inviim where imid='A' order by improd ";

            return this.Query(sql);
        }

        /// <summary>
        /// 查询料号详情
        /// </summary>
        public DataRow GetItemDetail(string no)
        {
            string sql = string.Format(" SELECT * FROM inviim where imid='A' and improd=N'{0}' ", no);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 新增仓库
        /// </summary>
        public int InsertWarehouse(string no, string desc, string area, string prov, string city, string addr1, string addr2, string addr3, string phone1, string phone2, string phone3, string fax1, string fax2, string email1, string email2, string contact, string property, string company)
        {
            string sql = string.Format("insert into inviwm(wmid,wmwhs,wmdes,wmarea,wmprov,wmcoty,wmadr1,wmadr2,wmadr3,wmpon1,wmpon2,wmpon3,wmfax1,wmfax2,wmmal1,wmmal2,wmctat,wmtyp,wmranh) VALUES('A',N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}',N'{7}',N'{8}',N'{9}',N'{10}',N'{11}',N'{12}',N'{13}',N'{14}',N'{15}',N'{16}',N'{17}') ", no, desc, area, prov, city, addr1, addr2, addr3, phone1, phone2, phone3, fax1, fax2, email1, email2, contact, property, company);
            return this.Execute(sql);
        }

        /// <summary>
        /// 更新仓库
        /// </summary>
        public int UpdateWarehouse(string no, string desc, string area, string prov, string city, string addr1, string addr2, string addr3, string phone1, string phone2, string phone3, string fax1, string fax2, string email1, string email2, string contact, string property, string company)
        {
            string sql = string.Format("update inviwm set wmdes=N'{1}',wmarea=N'{2}',wmprov=N'{3}',wmcoty=N'{4}',wmadr1=N'{5}',wmadr2=N'{6}',wmadr3=N'{7}',wmpon1=N'{8}',wmpon2=N'{9}',wmpon3=N'{10}',wmfax1=N'{11}',wmfax2=N'{12}',wmmal1=N'{13}',wmmal2=N'{14}',wmctat=N'{15}',wmtyp=N'{16}',wmranh=N'{17}' where wmwhs=N'{0}' ", no, desc, area, prov, city, addr1, addr2, addr3, phone1, phone2, phone3, fax1, fax2, email1, email2, contact, property, company);
            return this.Execute(sql);
        }

        /// <summary>
        /// 删除仓库
        /// </summary>
        public int DeleteWarehouse(string no)
        {
            string sql = string.Format("delete from inviwm where wmwhs=N'{0}' ", no);
            return this.Execute(sql);
        }

        /// <summary>
        /// 新增料号
        /// </summary>
        public int InsertItemInfo(string improd, string imdes, string imname, string imfac, string immode, string imvnam, string imium, string impum, string imcvr, string imtyp, string imukg, string imusiz, string immax, string immin, string imsct, string imact, string imnct, string imabc, string imbpc, string imkpc, string imltm, string imllc, DateTime imdte, DateTime imtim, string imusr)
        {
            string sql = @"INSERT INTO dbo.inviim 
                VALUES (@imid, @improd, @imdes, @imname, @imfac, @immode, @imvnam, @imium, @impum, @imcvr, @imtyp, @imukg, @imusiz, @immax, @immin, @imsct, @imact, @imnct, @imabc, @imbpc, @imkpc, @imltm, @imllc, @imdte, @imtim, @imusr)";

            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@imid", "A"));
            paras.Add(new SqlParameter("@improd", improd));
            paras.Add(new SqlParameter("@imdes", imdes));
            paras.Add(new SqlParameter("@imname", imname));
            paras.Add(new SqlParameter("@imfac", imfac));
            paras.Add(new SqlParameter("@immode", immode));
            paras.Add(new SqlParameter("@imvnam", imvnam));
            paras.Add(new SqlParameter("@imium", imium));
            paras.Add(new SqlParameter("@impum", impum));
            paras.Add(new SqlParameter("@imcvr", imcvr));
            paras.Add(new SqlParameter("@imtyp", imtyp));
            paras.Add(new SqlParameter("@imukg", imukg));
            paras.Add(new SqlParameter("@imusiz", imusiz));
            paras.Add(new SqlParameter("@immax", immax));
            paras.Add(new SqlParameter("@immin", immin));
            paras.Add(new SqlParameter("@imsct", imsct));
            paras.Add(new SqlParameter("@imact", imact));
            paras.Add(new SqlParameter("@imnct", imnct));
            paras.Add(new SqlParameter("@imabc", imabc));
            paras.Add(new SqlParameter("@imbpc", imbpc));
            paras.Add(new SqlParameter("@imkpc", imkpc));
            paras.Add(new SqlParameter("@imltm", imltm));
            paras.Add(new SqlParameter("@imllc", imllc));
            paras.Add(new SqlParameter("@imdte", imdte));
            paras.Add(new SqlParameter("@imtim", imtim));
            paras.Add(new SqlParameter("@imusr", imusr));
            return this.Execute(sql, paras.ToArray());
        }

        /// <summary>
        /// 更新料号
        /// </summary>
        public int UpdateItemInfo(string improd, string imdes, string imname, string imfac, string immode, string imvnam, string imium, string impum, string imcvr, string imtyp, string imukg, string imusiz, string immax, string immin, string imsct, string imact, string imnct, string imabc, string imbpc, string imkpc, string imltm, string imllc, DateTime imdte, DateTime imtim, string imusr)
        {
            string sql = @"UPDATE dbo.inviim
                            SET imdes = @imdes,
	                            imname = @imname,
	                            imfac = @imfac,
	                            immode = @immode,
	                            imvnam = @imvnam,
	                            imium = @imium,
	                            impum = @impum,
	                            imcvr = @imcvr,
	                            imtyp = @imtyp,
	                            imukg = @imukg,
	                            imusiz = @imusiz,
	                            immax = @immax,
	                            immin = @immin,
	                            imsct = @imsct,
	                            imact = @imact,
	                            imnct = @imnct,
	                            imabc = @imabc,
	                            imbpc = @imbpc,
	                            imkpc = @imkpc,
	                            imltm = @imltm,
	                            imllc = @imllc,
	                            imdte = @imdte,
	                            imtim = @imtim,
	                            imusr = @imusr
                            where improd=@improd
                            ";

            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@improd", improd));
            paras.Add(new SqlParameter("@imdes", imdes));
            paras.Add(new SqlParameter("@imname", imname));
            paras.Add(new SqlParameter("@imfac", imfac));
            paras.Add(new SqlParameter("@immode", immode));
            paras.Add(new SqlParameter("@imvnam", imvnam));
            paras.Add(new SqlParameter("@imium", imium));
            paras.Add(new SqlParameter("@impum", impum));
            paras.Add(new SqlParameter("@imcvr", imcvr));
            paras.Add(new SqlParameter("@imtyp", imtyp));
            paras.Add(new SqlParameter("@imukg", imukg));
            paras.Add(new SqlParameter("@imusiz", imusiz));
            paras.Add(new SqlParameter("@immax", immax));
            paras.Add(new SqlParameter("@immin", immin));
            paras.Add(new SqlParameter("@imsct", imsct));
            paras.Add(new SqlParameter("@imact", imact));
            paras.Add(new SqlParameter("@imnct", imnct));
            paras.Add(new SqlParameter("@imabc", imabc));
            paras.Add(new SqlParameter("@imbpc", imbpc));
            paras.Add(new SqlParameter("@imkpc", imkpc));
            paras.Add(new SqlParameter("@imltm", imltm));
            paras.Add(new SqlParameter("@imllc", imllc));
            paras.Add(new SqlParameter("@imdte", imdte));
            paras.Add(new SqlParameter("@imtim", imtim));
            paras.Add(new SqlParameter("@imusr", imusr));
            return this.Execute(sql, paras.ToArray());
        }

        /// <summary>
        /// 删除料号
        /// </summary>
        public int DeleteItemInfo(string improd)
        {
            string sql = string.Format("delete from inviim where improd=N'{0}' ", improd);
            return this.Execute(sql);
        }


        #endregion

        #region 查询与报表

        /// <summary>
        /// 查询库存
        /// </summary>
        public DataTable GetInventoryList(string poNo, string poName, string whCode)
        {
            string sql = string.Empty;
            sql = @"select * from inventory,inviwm where whcode=wmwhs ";

            if (!string.IsNullOrWhiteSpace(poNo))
            {
                sql += string.Format(" and pono like N'%{0}%' ", poNo);
            }
            if (!string.IsNullOrWhiteSpace(poName))
            {
                sql += string.Format(" and poname like N'%{0}%' ", poName);
            }
            if (!string.IsNullOrWhiteSpace(whCode))
            {
                sql += string.Format(" and wmwhs = N'{0}' ", whCode);
            }
            sql += " order by pono,whcode ";

            return this.Query(sql);
        }

        /// <summary>
        /// 查询收货
        /// </summary>
        public DataTable GetRTRecordList(string rtNoFrom, string rtNoTo, string orNoFrom, string orNoTo, string bomNoFrom, string bomNoTo, string venNoFrom, string venNoTo, string odNoFrom, string odNoTo, string dateFrom, string dateTo, string stationFrom, string stationTo)
        {
            StringBuilder sb = new StringBuilder("select * from trtrtm,purprl where RTID='RN' and rtprno=prlno and rtprseq=prlseq");


            if (!string.IsNullOrWhiteSpace(rtNoFrom))
            {
                sb.AppendFormat(" and rtrno>=N'{0}' ", rtNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(rtNoTo))
            {
                sb.AppendFormat(" and rtrno<=N'{0}' ", rtNoTo);
            }
            if (!string.IsNullOrWhiteSpace(orNoFrom))
            {
                sb.AppendFormat(" and rtsono>=N'{0}' ", orNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(orNoTo))
            {
                sb.AppendFormat(" and rtsono<=N'{0}' ", orNoTo);
            }
            if (!string.IsNullOrWhiteSpace(bomNoFrom))
            {
                sb.AppendFormat(" and RTTNO>=N'{0}' ", bomNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(bomNoTo))
            {
                sb.AppendFormat(" and RTTNO<=N'{0}' ", bomNoTo);
            }
            if (!string.IsNullOrWhiteSpace(venNoFrom))
            {
                sb.AppendFormat(" and RTVEND>=N'{0}' ", venNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(venNoTo))
            {
                sb.AppendFormat(" and RTVEND<=N'{0}' ", venNoTo);
            }
            if (!string.IsNullOrWhiteSpace(odNoFrom))
            {
                sb.AppendFormat(" and RTPRNO>=N'{0}' ", odNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(odNoTo))
            {
                sb.AppendFormat(" and RTPRNO<=N'{0}' ", odNoTo);
            }

            sb.AppendFormat(" and RTCDTE between N'{0}' and N'{1}' ", dateFrom, dateTo);

            if (!string.IsNullOrWhiteSpace(stationFrom))
            {
                sb.AppendFormat(" and rtproc>=N'{0}' ", stationFrom);
            }
            if (!string.IsNullOrWhiteSpace(stationTo))
            {
                sb.AppendFormat(" and rtproc<=N'{0}' ", stationTo);
            }
            sb.Append(" order by rtrno,rtseq ");

            return this.Query(sb.ToString());
        }

        /// <summary>
        /// 查询领用报废
        /// </summary>
        public DataTable GetPickRecordList(string orNoFrom, string orNoTo, string bomNo, string bomName, string dateFrom, string dateTo, bool isPick, bool isScrap)
        {
            StringBuilder sb = new StringBuilder("select * from invmi where 1=1 ");
            string con = string.Empty;
            if (isPick)
            {
                con = " and mino like 'MI%' ";
            }
            if (isScrap)
            {
                con = " and mino like 'SR%' ";
            }
            if (isPick && isScrap)
            {
                con = string.Empty;
            }
            sb.Append(con);

            if (!string.IsNullOrWhiteSpace(orNoFrom))
            {
                sb.AppendFormat(" and misono>=N'{0}' ", orNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(orNoTo))
            {
                sb.AppendFormat(" and misono<=N'{0}' ", orNoTo);
            }
            if (!string.IsNullOrWhiteSpace(bomNo))
            {
                sb.AppendFormat(" and mipono like N'%{0}%' ", bomNo);
            }
            if (!string.IsNullOrWhiteSpace(bomName))
            {
                sb.AppendFormat(" and miponame like N'%{0}%' ", bomName);
            }

            sb.AppendFormat(" and midate between N'{0}' and N'{1}' ", dateFrom, dateTo);
            sb.Append(" order by midate ");

            return this.Query(sb.ToString());
        }

        /// <summary>
        /// 查询收货类型
        /// </summary>
        public DataRow GetRTType(string rtNo)
        {
            string sql = string.Format(" select distinct prhtyp from purprh,trtrtm where prhno=rtprno and rtrno=N'{0}' ", rtNo);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取收货打印信息- N一般 F外包 S材料
        /// </summary>
        public DataTable GetRTMainPrint(string rtType, string rtNo)
        {
            string sql = string.Empty;
            if (rtType != "S")
            {
                sql = string.Format("SELECT prlno, prlqty, prlvnd, prlvndm, prlsord, prltno,prlstation,prlrtq,rtrno,rtfac,rtwhs,rtqinv,rtiump,rtcdte,bomnam,prlqty - prlrtq rmdqty,rtinv from purprl,tsfcbom,trtrtm where prlseq=rtprseq and prlno=rtprno and prlseq=bomseq and prlsord=bomwno and rtrno=N'{0}' order by rtrno,rtseq ", rtNo);
            }
            else
            {
                sql = string.Format("SELECT prlqty,prlvnd,prlvndm,prltno,prlstation,prlrtq,prloutno,rtrno,rtfac,rtwhs,rtqinv,rtiump,rtcdte,prhno,prlno,prlqty - prlrtq rmdqty from trtrtm,purprl,purprh where prhtyp='S' and rtprno=prlno and prhno=prlno and prlseq=rtprseq and rtrno=N'{0}' order by rtrno,rtseq ", rtNo);
            }

            return this.Query(sql);
        }

        /// <summary>
        /// 获取收货单打印信息- N一般 F委外 S材料
        /// </summary>
        public DataTable GetRTOrderPrint(string rtType, string rtNoFrom, string rtNoTo)
        {
            StringBuilder sql = new StringBuilder("");
            if (rtType != "S")
            {
                sql.Append("select prlvnd,prlvndm,avnam,bomnam,rtrno,rtfac,rtwhs, prlsord, prltno,prlstation,rtprno,rttno,rtvend, rtqinv,rtcdte,rtinv,rtiump,prlqty - prlrtq rmdqty,prlno from puravm,purprl,trtrtm,tsfcbom where avend=prlvnd and prlseq=rtprseq and prlno=rtprno and prlsoseq=bomseq and prlsord=bomwno ");

                if (rtType == "N") //一般收货
                {
                    sql.Append(" and rttyp='00' ");
                }
                else if (rtType == "F") //外包收货
                {
                    sql.Append(" and rttyp='01' ");
                }
            }
            else
            {
                //材料收货
                sql.Append("SELECT prlqty,prlvnd,prlvndm,prltno,prlstation,prlrtq,prloutno,rtrno,rtfac,rtwhs,rtqinv,rtiump,rtcdte,prhno,prlqty - prlrtq rmdqty,prlno from trtrtm,purprl,purprh where prhtyp='S' and rtprno=prlno and prhno=prlno and prlseq=rtprseq ");
            }

            if (!string.IsNullOrWhiteSpace(rtNoFrom))
            {
                sql.AppendFormat(" and rtrno>='{0}' ", rtNoFrom);
            }
            if (!string.IsNullOrWhiteSpace(rtNoTo))
            {
                sql.AppendFormat(" and rtrno<='{0}' ", rtNoTo);
            }

            sql.Append(" order by rtrno,rtseq ");

            return this.Query(sql.ToString());
        }

        #endregion

        #region 厂商、配置

        /// <summary>
        /// 查询基础配置 WH仓库属性 UM单位 交易条件TC 币别CY 区域AR 付款方式PY 业务员CK 税率TX
        /// </summary>
        public DataTable GetBaseParam(string type, string code)
        {
            string sql = string.Format(" select * from baseparameter where type=N'{0}'", type);
            if (!string.IsNullOrWhiteSpace(code))
            {
                sql += string.Format(" and code=N'{0}' ", code);
            }
            sql += " order by code ";
            return this.Query(sql);
        }

        /// <summary>
        /// 获取厂商列表
        /// </summary>
        /// <param name="type">1一般 2外包</param>
        /// <param name="orNo">工令号</param>
        /// <returns></returns>
        public DataTable GetVendors(int type, string orNo)
        {
            string sql = string.Format(" select distinct prlvnd,prlvndm,prlcur from puravm,purprl,purprh where prhno=prlno and prlpmt='Y' and avend=prlvnd and prlsord=N'{0}' and prlqty>prlqcqty+prlrtq ", orNo);
            if (type == 1)
            {
                sql += " and prhtyp in('N','S') ";
            }
            if (type == 2)
            {
                sql += " and prhtyp='F' ";
            }
            sql += " order by prlvnd ";
            return this.Query(sql);
        }

        /// <summary>
        /// 查询厂商详情
        /// </summary>
        public DataRow GetVendor(string no)
        {
            string sql = string.Format(" SELECT * FROM puravm where avend=N'{0}' ", no);

            var table = this.Query(sql);
            if (table != null && table.Rows.Count >= 1)
            {
                return table.Rows[0];
            }
            else
            {
                return null;
            }
        }

        #endregion

    }
}